﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
using AccessProvider;

namespace Interface.Diem
{
    public partial class DiemHK : Form
    {

        DataConnection dc = new DataConnection();
        BusinessLogic bl = new BusinessLogic();
        public DiemHK()
        {
            InitializeComponent();
        }

        private void BangDiem_Load(object sender, EventArgs e)
        {

            string sqlHS = "SELECT * FROM HOCSINH";
            DataTable dths = dc.getData(sqlHS);
            if (dths.Rows.Count > 0)
            {
                for (int i = 0; i < dths.Rows.Count; i++)
                {
                    int stt = i + 1;
                    DataRow rowhs = dths.Rows[i];
                    string maHS = rowhs["MaHS"].ToString();
                    string tenHS = rowhs["HoTen"].ToString();
                    int maLop = Convert.ToInt32(rowhs["MaLop"].ToString());
                    string sqltenlop = "SELECT TENLOP FROM LOP WHERE MaLop=" +maLop+"";
                    string tenLop = dc.getStringValue(sqltenlop);

                    decimal dtb1 = 0;
                    decimal dtb2 = 0;
                    decimal tongdiem1 = 0;
                    decimal tongdiem2 = 0;
                    string sqlMH = "SELECT * FROM MONHOC";
                    DataTable dtmh = dc.getData(sqlMH);
                    if (dtmh.Rows.Count > 0)
                    {
                        decimal deTBm1 = 0;
                        decimal deTBm2 = 0;
                        for (int j = 0; j < dtmh.Rows.Count; j++)
                        {
                            DataRow rowmh = dtmh.Rows[j];
                            string maMH = rowmh["MaMH"].ToString();
                            string sqldtbm1 = "SELECT * FROM DIEM WHERE MaHS='" + maHS + "' AND MaMH ='" + maMH + "'AND MaLop='" + maLop + "'AND HocKy = 1";
                            DataTable dtTBm1 = dc.getData(sqldtbm1);
                            if (dtTBm1.Rows.Count > 0)
                            {
                                decimal diem15p = Convert.ToDecimal(dtTBm1.Rows[0]["Diem15p"].ToString());
                                decimal diem1t = Convert.ToDecimal(dtTBm1.Rows[0]["Diem1Tiet"].ToString());
                                deTBm1 = (diem15p + diem1t * 2) / 3;

                                tongdiem1 = tongdiem1 + deTBm1;
                            }
                            string sqldtbm2 = "SELECT * FROM DIEM WHERE MaHS='" + maHS + "' AND MaMH ='" + maMH + "'AND MaLop='" + maLop + "'AND HocKy = 2";
                            DataTable dtTBm2 = dc.getData(sqldtbm2);
                            if (dtTBm2.Rows.Count > 0)
                            {
                                decimal diem15p = Convert.ToDecimal(dtTBm2.Rows[0]["Diem15p"].ToString());
                                decimal diem1t = Convert.ToDecimal(dtTBm2.Rows[0]["Diem1Tiet"].ToString());
                                deTBm2 = (diem15p + diem1t * 2) / 3;

                                tongdiem2 = tongdiem2 + deTBm2;
                            }
                        }                     
                    }
                    dtb1 = Math.Round(tongdiem1/(dtmh.Rows.Count),2);                 
                    dtb2 = Math.Round(tongdiem2/(dtmh.Rows.Count),2);
 
                    ListViewItem item = new ListViewItem(stt.ToString());
                    item.SubItems.Add(tenHS);
                    item.SubItems.Add(tenLop);
                    item.SubItems.Add(dtb1.ToString());
                    item.SubItems.Add(dtb2.ToString());
                    lv_diem.Items.Add(item);

                }
            }

        }
        protected DataTable dtInitiate()
        {
            DataTable dt = new DataTable();
            DataColumn col = dt.Columns.Add("STT", typeof(string));
            col.AutoIncrement = true;
            col.AutoIncrementStep = 1;
            col.AutoIncrementSeed = 1;
            dt.Columns.Add("HoTen", typeof(string));
            dt.Columns.Add("Lop", typeof(string));
            dt.Columns.Add("DiemTBHK1", typeof(string));
            dt.Columns.Add("DiemTBHK2", typeof(string));
            return dt;
        }  
    }
}
